package com.nxcy.student.dao;

import com.nxcy.common.util.StringUtil;
import com.nxcy.common.bean.PagerInfo;
import com.nxcy.mysql.DBUtils;
import com.nxcy.student.bean.Student;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class StudentDaoImpl implements StudentDao {
    /***
     * 获取最大的学号
     * @return
     */
    @Override
    public Long getMaxSno() {
        DBUtils db = new DBUtils();
        long sno = 0L;
        try {
            String sql = "SELECT MAX(sno)+1 as maxsno from t_user";
            ResultSet rs = db.executeQuery(sql);
            if (rs.next()) {
                sno = rs.getLong("maxsno");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            db.close();
        }
        return sno;
    }

    /***
     * 添加学生
     * @param student
     * @return
     */
    @Override
    public int addStudent(Student student) {
        DBUtils db = new DBUtils();
        int num = 0;
        try {
            PreparedStatement pstmt = db.getPreparedStatement("insert into t_user values(0,?,?,?,?,?,?,?,?)");
            pstmt.setString(1, student.getSno());
            pstmt.setString(2, student.getName());
            pstmt.setString(3, student.getSex());
            pstmt.setString(4, student.getPhone());
            pstmt.setString(5, student.getUsername());
            pstmt.setString(6, student.getPasssword());
            pstmt.setString(7, student.getClazz());
            pstmt.setString(8, student.getHomePath());
            num = pstmt.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            db.close();
        }
        return num;
    }

    /***
     * 通过ID删除一个学生
     * @param id
     * @return
     */
    @Override
    public int deleteStudentById(int id) {
        DBUtils db = new DBUtils();
        int num = 0;
        try {
            String sql = "DELETE FROM  t_user where id in (" + id + ")";
            num = db.executeUpdate(sql);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            db.close();
        }
        return num;
    }

    /***
     * 删除多个学生
     * @param ids
     * @return
     */
    @Override
    public int deleteStudentByIds(int... ids) {
        DBUtils db = new DBUtils();
        int num = 0;
        try {
            String id = "";
            for (int i : ids) {
                id += i + ",";
            }
            id = id.substring(0, id.length() - 1);
            String sql = "DELETE FROM  t_user where id in (" + id + ")";
            num = db.executeUpdate(sql);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            db.close();
        }
        return num;
    }

    /***
     * 通过ID查找一个学生
     * @param id
     * @return
     */
    @Override
    public Student getStudentById(int id) {
        Student student = null;
        DBUtils db = new DBUtils();
        try {
            String sql = "SELECT * from t_user where id=" + id;
            ResultSet rs = db.executeQuery(sql);
            if (rs.next()) {
                student = new Student();
                student.setId(rs.getInt("id"));
                student.setSno(rs.getString("sno"));
                student.setName(rs.getString("name"));
                student.setSex(rs.getString("sex"));
                student.setClazz(rs.getString("clazz"));
                student.setHomePath(rs.getString("home_path"));
                student.setPhone(rs.getString("phone"));
                student.setUsername(rs.getString("username"));
                student.setPasssword(rs.getString("password"));
            }
        } catch (Exception e) {
            e.printStackTrace();
            student = null;
        } finally {
            db.close();
        }
        return student;
    }

    /***
     * 更具条件查找多个学生
     * @param student
     * @return
     */
    @Override
    public List<Student> queryStudent(Student student) {
        List<Student> list = new ArrayList<>();
        DBUtils db = new DBUtils();
        String datasql = "SELECT * from t_user where 1=1 ";
        String sql = "";
        String sno = student.getSno();
        if (sno != null && sno.length() > 0) {
            sql += " and sno like '%" + sno + "%'";
        }
        String name = student.getName();
        if (!StringUtil.isEmpty(name)) {
            sql += " and name like '%" + name + "%'";
        }
        String sex = student.getSex();
        if (!StringUtil.isEmpty(sex)) {
            sql += " and sex = '" + sex + "'";
        }
        String phone = student.getPhone();
        if (!StringUtil.isEmpty(phone)) {
            sql += " and phone like '%" + phone + "%'";
        }
        String username = student.getUsername();
        if (!StringUtil.isEmpty(username)) {
            sql += " and username = '" + username + "'";
        }
        String password = student.getPasssword();
        if (!StringUtil.isEmpty(password)) {
            sql += " and password = '" + password + "'";
        }
        String clazz = student.getClazz();
        if (!StringUtil.isEmpty(clazz)) {
            sql += " and clazz = '" + clazz + "'";
        }
        String homePath = student.getHomePath();
        if (!StringUtil.isEmpty(clazz)) {
            sql += " and home_path = '" + homePath + "'";
        }
        String querySql = datasql + sql;
        try {
            ResultSet rs = db.executeQuery(querySql);
            while (rs.next()) {
                Student s = new Student();
                s.setId(rs.getInt("id"));
                s.setSno(rs.getString("sno"));
                s.setName(rs.getString("name"));
                s.setSex(rs.getString("sex"));
                s.setClazz(rs.getString("clazz"));
                s.setHomePath(rs.getString("home_path"));
                s.setPhone(rs.getString("phone"));
                s.setUsername(rs.getString("username"));
                s.setPasssword(rs.getString("password"));
                list.add(s);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public PagerInfo<Student> queryStudentByPage(Student student) {
        List<Student> list = new ArrayList<>();
        DBUtils db = new DBUtils();
        String datasql = "SELECT * from t_user where 1=1 ";
        String countsql = "SELECT count(*) as count from t_user where 1=1";
        String sql = "";
        String sno = student.getSno();
        if (sno != null && sno.length() > 0) {
            sql += " and sno like '%" + sno + "%'";
        }
        String name = student.getName();
        if (!StringUtil.isEmpty(name)) {
            sql += " and name like '%" + name + "%'";
        }
        String sex = student.getSex();
        if (!StringUtil.isEmpty(sex)) {
            sql += " and sex = '" + sex + "'";
        }
        String phone = student.getPhone();
        if (!StringUtil.isEmpty(phone)) {
            sql += " and phone like '%" + phone + "%'";
        }
        String username = student.getUsername();
        if (!StringUtil.isEmpty(username)) {
            sql += " and username = '" + username + "'";
        }
        String password = student.getPasssword();
        if (!StringUtil.isEmpty(password)) {
            sql += " and password = '" + password + "'";
        }
        String clazz = student.getClazz();
        if (!StringUtil.isEmpty(clazz)) {
            sql += " and clazz = '" + clazz + "'";
        }
        String homePath = student.getHomePath();
        if (!StringUtil.isEmpty(clazz)) {
            sql += " and home_path = '" + homePath + "'";
        }
        PagerInfo<Student> pagerInfo = new PagerInfo<Student>();
        int totalNum = 0;
        try {
            ResultSet rs = db.executeQuery(countsql + sql);
            if (rs.next()) {
                totalNum = rs.getInt("count");
            }
            pagerInfo.getPager().setTotalNum(totalNum);
            pagerInfo.getPager().setTotalPage(totalNum % student.getPager().getPageSize() == 0 ? totalNum / student.getPager().getPageSize() : totalNum / student.getPager().getPageSize() + 1);
            pagerInfo.getPager().setPageNum(student.getPager().getPageNum());
        } catch (Exception e) {
            e.printStackTrace();
        }
        sql += " limit " + (student.getPager().getPageNum() - 1) * student.getPager().getPageSize() + "," + student.getPager().getPageSize();
        String querySql = datasql + sql;
        try {
            ResultSet rs = db.executeQuery(querySql);
            while (rs.next()) {
                Student s = new Student();
                s.setId(rs.getInt("id"));
                s.setSno(rs.getString("sno"));
                s.setName(rs.getString("name"));
                s.setSex(rs.getString("sex"));
                s.setClazz(rs.getString("clazz"));
                s.setHomePath(rs.getString("home_path"));
                s.setPhone(rs.getString("phone"));
                s.setUsername(rs.getString("username"));
                s.setPasssword(rs.getString("password"));
                list.add(s);
            }
            pagerInfo.setList(list);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return pagerInfo;
    }

    /***
     * 修改学生
     * @param student
     * @return
     */
    @Override
    public int updateStudent(Student student) {
        if (student == null || student.getId() == 0) {
            return 0;
        }
        DBUtils db = new DBUtils();
        PreparedStatement pstmt = null;
        try {
            pstmt = db.getPreparedStatement("UPDATE t_user set sno=?,name=?,sex=?,phone=?,username=?,password=?,clazz=?,home_path=? where id=?");
            pstmt.setString(1, student.getSno());
            pstmt.setString(2, student.getName());
            pstmt.setString(3, student.getSex());
            pstmt.setString(4, student.getPhone());
            pstmt.setString(5, student.getUsername());
            pstmt.setString(6, student.getPasssword());
            pstmt.setString(7, student.getClazz());
            pstmt.setString(8, student.getHomePath());
            pstmt.setInt(9, student.getId());
            return pstmt.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return 0;
    }
}
